<?php

$dsn = 'mysql:dbname=weather;host=sajb.co.uk';
$user = 'weatheruser';
$password = 'dragon32';
try {
//      error_log("Trying to Connect");
    $dbh = new PDO($dsn, $user, $password);
} catch (PDOException $e) {
    echo 'Connection failed: ' . $e->getMessage();
    die("Died in Connection");
}

$sql = "SELECT MONTHNAME(dateutc) AS Date, 
round(AVG(temp),2) as Temp,
round(AVG(windspeedmph),2) AS WindSpeed,
round(AVG(barom),2) AS Pressure,
round(AVG(rain),2) AS AverageRain,
round(AVG(humidity),2) AS Humidity,
round(AVG(winddir),2) AS WindDirection, 
round(AVG(windgustmph),2) AS WindGust,
round(AVG(dewpt),2) AS FeelsLike,
round(SUM(rain),2) AS TotalRain
FROM raw 
WHERE YEAR(dateutc) = YEAR(NOW())
GROUP BY MONTH(dateutc)
ORDER BY dateutc DESC LIMIT 12;";
try {
    $sth = $dbh->prepare($sql);
} catch (PDOException $e) {
    echo 'Prepare failed: ' . $e->getMessage();
    die("Died in Prepare");
}

try {
    $sth->execute();
} catch (PDOException $e) {
    echo 'Execution failed: ' . $e->getMessage();
    die("Died in execute");
}

$tableData = $sth->fetchAll(PDO::FETCH_ASSOC);
$jsonData = json_encode($tableData, JSON_NUMERIC_CHECK);

print $jsonData;
/*
[
  {
      "Date": "04/27/2013 15:14",
      "Time": " 15:14 BST ",
      "TempOut": 11.2,
      "FeelsLike": 8.8,
      "HumidityOut": 53,
      "WindDirection": " NE ",
      "WindAvg": 1,
      "WindGust": 2,
      "Rain": 0.0,
      "AbsPressure": 1010.0  },
 ]
*/ 
